---
title: "Native Query Result to POJO"
categories:
- java
- spring-jpa
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#org19c9b30">Using Mapped Entities (JPA 2.0)</a></li>
<li><a href="#org5f39270">Manual Mapping</a></li>
<li><a href="#org6c4ab73">JPA 2.1 with @SqlResultSetMapping</a></li>
<li><a href="#org8619079">Using XML Mapping</a></li>
<li><a href="#org957bdfd">Transformers (Deprecated)</a></li>
<li><a href="#org1b16633">CreateNativeQuery without type</a></li>
</ul>
</div>
</div>
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection">https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection</a>
</p>
</blockquote>
<p>
{% raw %} SqlResultSetMapping {% endraw %} 能将native query返回的对象映射到Entity
</p>
<p>
JPA 1.0 does not allow mapping to non-entity classes.
Only in JPA 2.1 a {% raw %} ConstructorResult {% endraw %} has been added to map return values a java class.
</p>
<div class="outline-2" id="outline-container-org19c9b30">
<h2 id="org19c9b30">Using Mapped Entities (JPA 2.0)</h2>
<div class="outline-text-2" id="text-org19c9b30">
<p>
Using JPA 2.0 it is not possible to map a native query to a POJO, it can only be done with an entity.
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">in this case, Jedi, must be a mapped entity class.</span>
<span style="font-weight: bold; text-decoration: underline;">Query</span> <span style="font-weight: bold; font-style: italic;">query</span> = em.createNativeQuery(<span style="font-style: italic;">"SELECT name,age FROM jedi_table"</span>, Jedi.<span style="font-weight: bold;">class</span>);
<span style="font-weight: bold; text-decoration: underline;">@SuppressWarnings</span>(<span style="font-style: italic;">"unchecked"</span>)
<span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt; <span style="font-weight: bold; font-style: italic;">items</span> = (<span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt;) query.getResultList();
</pre>
</div>
<p>
An alternative to avoid the unchecked warning here, would be to use a named native query. So if we declare the native query in an entity
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@NamedNativeQuery</span>(
 name=<span style="font-style: italic;">"jedisQry"</span>, 
 query = <span style="font-style: italic;">"SELECT name,age FROM jedis_table"</span>, 
 resultClass = Jedi.<span style="font-weight: bold;">class</span>)
</pre>
</div>
<p>
Then, we can simply do:
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">TypedQuery</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt; <span style="font-weight: bold; font-style: italic;">query</span> = em.createNamedQuery(<span style="font-style: italic;">"jedisQry"</span>, Jedi.<span style="font-weight: bold;">class</span>);
<span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt; <span style="font-weight: bold; font-style: italic;">items</span> = query.getResultList();
</pre>
</div>
<p>
This is safer, but we are still restricted to use a mapped entity.
</p>
</div>
</div>
<div class="outline-2" id="outline-container-org5f39270">
<h2 id="org5f39270">Manual Mapping</h2>
<div class="outline-text-2" id="text-org5f39270">
<p>
A solution I experimented a bit (before the arrival of JPA 2.1) was doing mapping against a POJO constructor using a bit of reflection.
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold;">public</span> <span style="font-weight: bold;">static</span> &lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; text-decoration: underline;">T</span> <span style="font-weight: bold;">map</span>(<span style="font-weight: bold; text-decoration: underline;">Class</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; font-style: italic;">type</span>, <span style="font-weight: bold; text-decoration: underline;">Object</span>[] <span style="font-weight: bold; font-style: italic;">tuple</span>){
   <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Class</span>&lt;?&gt;&gt; <span style="font-weight: bold; font-style: italic;">tupleTypes</span> = <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">ArrayList</span>&lt;&gt;();
   <span style="font-weight: bold;">for</span>(<span style="font-weight: bold; text-decoration: underline;">Object</span> <span style="font-weight: bold; font-style: italic;">field</span> : tuple){
      tupleTypes.add(field.getClass());
   }
   <span style="font-weight: bold;">try</span> {
      <span style="font-weight: bold; text-decoration: underline;">Constructor</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; font-style: italic;">ctor</span> = type.getConstructor(tupleTypes.toArray(<span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">Class</span>&lt;?&gt;[tuple.length]));
      <span style="font-weight: bold;">return</span> ctor.newInstance(tuple);
   } <span style="font-weight: bold;">catch</span> (<span style="font-weight: bold; text-decoration: underline;">Exception</span> <span style="font-weight: bold; font-style: italic;">e</span>) {
      <span style="font-weight: bold;">throw</span> <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">RuntimeException</span>(e);
   }
}
</pre>
</div>
<p>
This method basically takes a tuple array (as returned by native queries) and maps it against a provided POJO class by looking for a constructor that has the same number of fields and of the same type.
</p>
<p>
Then we can use convenient methods like:
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold;">public</span> <span style="font-weight: bold;">static</span> &lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold;">map</span>(<span style="font-weight: bold; text-decoration: underline;">Class</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; font-style: italic;">type</span>, <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Object</span>[]&gt; <span style="font-weight: bold; font-style: italic;">records</span>){
   <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; font-style: italic;">result</span> = <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">LinkedList</span>&lt;&gt;();
   <span style="font-weight: bold;">for</span>(<span style="font-weight: bold; text-decoration: underline;">Object</span>[] <span style="font-weight: bold; font-style: italic;">record</span> : records){
      result.add(map(type, record));
   }
   <span style="font-weight: bold;">return</span> result;
}

<span style="font-weight: bold;">public</span> <span style="font-weight: bold;">static</span> &lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold;">getResultList</span>(<span style="font-weight: bold; text-decoration: underline;">Query</span> <span style="font-weight: bold; font-style: italic;">query</span>, <span style="font-weight: bold; text-decoration: underline;">Class</span>&lt;<span style="font-weight: bold; text-decoration: underline;">T</span>&gt; <span style="font-weight: bold; font-style: italic;">type</span>){
  <span style="font-weight: bold; text-decoration: underline;">@SuppressWarnings</span>(<span style="font-style: italic;">"unchecked"</span>)
  <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Object</span>[]&gt; <span style="font-weight: bold; font-style: italic;">records</span> = query.getResultList();
  <span style="font-weight: bold;">return</span> map(type, records);
}
</pre>
</div>
<p>
And we can simply use this technique as follows:
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">Query</span> <span style="font-weight: bold; font-style: italic;">query</span> = em.createNativeQuery(<span style="font-style: italic;">"SELECT name,age FROM jedis_table"</span>);
<span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt; <span style="font-weight: bold; font-style: italic;">jedis</span> = getResultList(query, Jedi.<span style="font-weight: bold;">class</span>);
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org6c4ab73">
<h2 id="org6c4ab73">JPA 2.1 with @SqlResultSetMapping</h2>
<div class="outline-text-2" id="text-org6c4ab73">
<p>
With the arrival of JPA 2.1, we can use the @SqlResultSetMapping annotation to solve the problem.
</p>
<p>
We need to declare a result set mapping somewhere in a entity:
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@SqlResultSetMapping</span>(name=<span style="font-style: italic;">"JediResult"</span>, classes = {
    <span style="font-weight: bold; text-decoration: underline;">@ConstructorResult</span>(targetClass = Jedi.<span style="font-weight: bold;">class</span>, 
    columns = {<span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"name"</span>), <span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"age"</span>)})
})
</pre>
</div>
<p>
And then we simply do:
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">Query</span> <span style="font-weight: bold; font-style: italic;">query</span> = em.createNativeQuery(<span style="font-style: italic;">"SELECT name,age FROM jedis_table"</span>, <span style="font-style: italic;">"JediResult"</span>);
<span style="font-weight: bold; text-decoration: underline;">@SuppressWarnings</span>(<span style="font-style: italic;">"unchecked"</span>)
<span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Jedi</span>&gt; <span style="font-weight: bold; font-style: italic;">samples</span> = query.getResultList();
</pre>
</div>
<p>
Of course, in this case Jedi needs not to be an <b>mapped entity</b>. It can be a regular POJO.
</p>
<p>
<a href="https://stackoverflow.com/questions/28945146/jpa-entity-without-underlying-table">More about ConstructorResult</a>
</p>
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/25188939/mapping-nativequery-results-into-a-pojo">https://stackoverflow.com/questions/25188939/mapping-nativequery-results-into-a-pojo</a>
</p>
</blockquote>
<p>
<span class="underline">但是 @SqlResultSetMapping 还是必须标注在 @Entity 实体上，否则将抛出 ResultSetMapping not found异常</span>
</p>
</div>
</div>
<div class="outline-2" id="outline-container-org8619079">
<h2 id="org8619079">Using XML Mapping</h2>
<div class="outline-text-2" id="text-org8619079">
<p>
I am one of those that find adding all these @SqlResultSetMapping pretty invasive in my entities, and I particularly dislike the definition of named queries within entities, so alternatively I do all this in the META-INF/orm.xml file:
</p>
<div class="org-src-container">
<pre class="src src-xml">
&lt;<span style="font-weight: bold;">named-native-query</span> <span style="font-weight: bold; font-style: italic;">name</span>=<span style="font-style: italic;">"GetAllJedi"</span> <span style="font-weight: bold; font-style: italic;">result-set-mapping</span>=<span style="font-style: italic;">"JediMapping"</span>&gt;
    &lt;<span style="font-weight: bold;">query</span>&gt;SELECT name,age FROM jedi_table&lt;/<span style="font-weight: bold;">query</span>&gt;
&lt;/<span style="font-weight: bold;">named-native-query</span>&gt;

&lt;<span style="font-weight: bold;">sql-result-set-mapping</span> <span style="font-weight: bold; font-style: italic;">name</span>=<span style="font-style: italic;">"JediMapping"</span>&gt;
        &lt;<span style="font-weight: bold;">constructor-result</span> <span style="font-weight: bold; font-style: italic;">target-class</span>=<span style="font-style: italic;">"org.answer.model.Jedi"</span>&gt;
            &lt;<span style="font-weight: bold;">column</span> <span style="font-weight: bold; font-style: italic;">name</span>=<span style="font-style: italic;">"name"</span> <span style="font-weight: bold; font-style: italic;">class</span>=<span style="font-style: italic;">"java.lang.String"</span>/&gt;
            &lt;<span style="font-weight: bold;">column</span> <span style="font-weight: bold; font-style: italic;">name</span>=<span style="font-style: italic;">"age"</span> <span style="font-weight: bold; font-style: italic;">class</span>=<span style="font-style: italic;">"java.lang.Integer"</span>/&gt;
        &lt;/<span style="font-weight: bold;">constructor-result</span>&gt;
    &lt;/<span style="font-weight: bold;">sql-result-set-mapping</span>&gt;
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org957bdfd">
<h2 id="org957bdfd">Transformers (Deprecated)</h2>
<div class="outline-text-2" id="text-org957bdfd">
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/26831893/createnativequery-mapping-to-pojo-non-entity">https://stackoverflow.com/questions/26831893/createnativequery-mapping-to-pojo-non-entity</a>
</p>
</blockquote>
<div class="org-src-container">
<pre class="src src-java">  <span style="font-weight: bold;">final</span> <span style="font-weight: bold; text-decoration: underline;">Query</span> <span style="font-weight: bold; font-style: italic;">query</span> = Sale.entityManager().createNativeQuery(...);

<span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">access to internal Hibernate of EntityManager</span>
        query.unwrap(SQLQuery.<span style="font-weight: bold;">class</span>)
                .addScalar(<span style="font-style: italic;">"total"</span>, <span style="font-weight: bold; text-decoration: underline;">LongType</span>.INSTANCE)
                .addScalar(<span style="font-style: italic;">"amountOfSales"</span>, <span style="font-weight: bold; text-decoration: underline;">LongType</span>.INSTANCE)
                .addScalar(<span style="font-style: italic;">"amountOfProducts"</span>, <span style="font-weight: bold; text-decoration: underline;">LongType</span>.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(SaleStatsInfo.<span style="font-weight: bold;">class</span>));

        ...
        query.getSingleResult();
</pre>
</div>
<blockquote>
<p>
<a href="https://vladmihalcea.com/the-best-way-to-map-a-projection-query-to-a-dto-with-jpa-and-hibernate/">https://vladmihalcea.com/the-best-way-to-map-a-projection-query-to-a-dto-with-jpa-and-hibernate/</a>
</p>
</blockquote>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">PostDTO</span>&gt; <span style="font-weight: bold; font-style: italic;">postDTOs</span> = entityManager
.createQuery(
    <span style="font-style: italic;">"select "</span> +
    <span style="font-style: italic;">"       p.id as id, "</span> +
    <span style="font-style: italic;">"       p.title as title "</span> +
    <span style="font-style: italic;">"from Post p "</span> +
    <span style="font-style: italic;">"where p.createdOn &gt; :fromTimestamp"</span>)
.setParameter( <span style="font-style: italic;">"fromTimestamp"</span>, Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( <span style="font-weight: bold; text-decoration: underline;">ZoneOffset</span>.UTC ) ))
.unwrap( <span style="font-weight: bold; text-decoration: underline;">org</span>.<span style="font-weight: bold; text-decoration: underline;">hibernate</span>.<span style="font-weight: bold; text-decoration: underline;">query</span>.Query.<span style="font-weight: bold;">class</span> )
.setResultTransformer( Transformers.aliasToBean( PostDTO.<span style="font-weight: bold;">class</span> ) )
.getResultList();
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org1b16633">
<h2 id="org1b16633">CreateNativeQuery without type</h2>
<div class="outline-text-2" id="text-org1b16633">
<blockquote>
<p>
<a href="https://codereview.stackexchange.com/questions/1409/jpa-query-to-return-a-map">https://codereview.stackexchange.com/questions/1409/jpa-query-to-return-a-map</a>
</p>
</blockquote>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold;">public</span> <span style="font-weight: bold; text-decoration: underline;">Map</span>&lt;<span style="font-weight: bold; text-decoration: underline;">String</span>, <span style="font-weight: bold; text-decoration: underline;">String</span>&gt; <span style="font-weight: bold;">getAvailableBorderTypes</span>() {
   <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">create empty map to store results in. If we don't find results, an empty hashmap will be returned</span>
   <span style="font-weight: bold; text-decoration: underline;">Map</span>&lt;<span style="font-weight: bold; text-decoration: underline;">String</span>, <span style="font-weight: bold; text-decoration: underline;">String</span>&gt; <span style="font-weight: bold; font-style: italic;">results</span> = <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">HashMap</span>&lt;<span style="font-weight: bold; text-decoration: underline;">String</span>, <span style="font-weight: bold; text-decoration: underline;">String</span>&gt;();

   <span style="font-weight: bold; text-decoration: underline;">EntityManager</span> <span style="font-weight: bold; font-style: italic;">em</span> = entityManagerFactory.createEntityManager();

   <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">Construct and run query</span>
   <span style="font-weight: bold; text-decoration: underline;">String</span> <span style="font-weight: bold; font-style: italic;">jpaQuery</span> = <span style="font-style: italic;">"SELECT DISTINCT location.mftcc, location.name FROM Location location"</span>;
   <span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Object</span>[]&gt; <span style="font-weight: bold; font-style: italic;">resultList</span> = em.createQuery(jpaQuery).getResultList();

   <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">Place results in map</span>
   <span style="font-weight: bold;">for</span> (<span style="font-weight: bold; text-decoration: underline;">Object</span>[] <span style="font-weight: bold; font-style: italic;">borderTypes</span>: resultList) {
      results.put((<span style="font-weight: bold; text-decoration: underline;">String</span>)borderTypes[0], (<span style="font-weight: bold; text-decoration: underline;">String</span>)borderTypes[1]);
   }

   <span style="font-weight: bold;">return</span> results;
}
</pre>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
